﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DTO = Microsoft.MSE.Catalog.Contracts;
using System.Xml;

namespace Microsoft.MSE.Catalog.Module.Data
{
    public class DataEntity
    {
        public DataEntity()
        {

        }

        public static DTO.DataEntity CreateDataEntity(DTO.DataEntity dataEntity, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[CreateDataEntity]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Direction = ParameterDirection.Output;
                    string dataEntityName = BuildDataEntityName(dataEntity);
                    dataEntity.Name = dataEntityName;
                    NameParam.Value = dataEntity.Name;

                    DescriptionParam.Value = dataEntity.Description;

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(DescriptionParam);

                    cmd.ExecuteNonQuery();
                    dataEntity.ID = (Guid)IDParam.Value;
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }


            return dataEntity;
        }

        public static void DeleteDataEntity(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeleteDataEntity]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
        }

        public static DTO.DataEntity UpdateDataEntity(DTO.DataEntity dataEntity, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[UpdateDataEntity]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = dataEntity.ID;
                    string dataEntityName = BuildDataEntityName(dataEntity);
                    dataEntity.Name = dataEntityName;
                    NameParam.Value = dataEntity.Name;
                    DescriptionParam.Value = dataEntity.Description;

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(DescriptionParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return dataEntity;
        }

        public static DTO.DataEntity GetDataEntity(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.DataEntity dataEntity = new DTO.DataEntity();
            const string sprocname = "[ServiceEngine].[GetDataEntity]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            dataEntity.ID = reader.GetGuid(0);
                            dataEntity.Name = reader.GetString(1);
                            if (reader.IsDBNull(2))
                            {
                                dataEntity.Description = null;
                            }
                            else
                            {
                                dataEntity.Description = reader.GetString(2);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return dataEntity;

        }

        public static DTO.DataEntity GetDataEntityBySchemaObjectID(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.DataEntity dataEntity = new DTO.DataEntity();
            const string sprocname = "[ServiceEngine].[GetDataEntityBySchemaObjectID]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            dataEntity.ID = reader.GetGuid(0);
                            dataEntity.Name = reader.GetString(1);
                            if (reader.IsDBNull(3))
                            {
                                dataEntity.Description = null;
                            }
                            else
                            {
                                dataEntity.Description = reader.GetString(3);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return dataEntity;

        }

        public static DTO.DataEntityList ListDataEntities(SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.DataEntityList list = new DTO.DataEntityList();
            const string sprocname = "[ServiceEngine].[ListDataEntities]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.DataEntities = new List<DTO.DataEntityListElement>();
                            while (reader.Read())
                            {
                                DTO.DataEntityListElement element = new DTO.DataEntityListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.DataEntities.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static List<DTO.DataEntityListElement> ListDataEntitiesByGroup(Guid entityGroupID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.DataEntityListElement> list = new List<DTO.DataEntityListElement>();
            const string sprocname = "[ServiceEngine].[ListEntitiesByGroup]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@entityGroupID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = entityGroupID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list = new List<DTO.DataEntityListElement>();
                            while (reader.Read())
                            {
                                DTO.DataEntityListElement element = new DTO.DataEntityListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }

                                list.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
            return list;

        }

        public static void AssociateSchemaObjectToDataEntity(Guid dataEntityID, string name, Guid schemaID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[AssociateSchemaObjectToDataEntity]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                //Note: No change tracking needed for newly activated operations at an endpoint
                //brokers will query for the operation to resolve since it won't be in the broker cache

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter DataEntityIDParam = new SqlParameter("@DataEntityID", SqlDbType.UniqueIdentifier);
                    SqlParameter SchemaIDParam = new SqlParameter("@SchemaID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar,256);
                    SqlParameter TypeParam = new SqlParameter("@Type", SqlDbType.SmallInt);


                    cmd.CommandType = CommandType.StoredProcedure;
                    DataEntityIDParam.Value = dataEntityID;
                    SchemaIDParam.Value = schemaID;
                    NameParam.Value = name;
                    TypeParam.Value = (Int16)DTO.SchemaObjectType.Element;

                    cmd.Parameters.Add(DataEntityIDParam);
                    cmd.Parameters.Add(SchemaIDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(TypeParam);

                    cmd.ExecuteNonQuery();
                }

                if ((autoclose) && (txn != null)) txn.Commit();

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection in opened in this method
            }
        }

        public static string BuildDataEntityName(DTO.DataEntity dataEntity)
        {
            string name = "";

            if (dataEntity.SchemaObjects != null && dataEntity.SchemaObjects.Count > 0)
            {
                foreach (DTO.SchemaObject schemaObject in dataEntity.SchemaObjects)
                {
                    if (string.IsNullOrEmpty(schemaObject.Name) == false &&
                        string.IsNullOrEmpty(schemaObject.NamespaceUri) == false)
                    {
                        name = string.Format("{0}:{1}", schemaObject.Name, schemaObject.NamespaceUri);
                        break;
                    }
                }
                if (string.IsNullOrEmpty(name) == true)
                {
                    throw new ApplicationException(string.Format("Could not create name for DataEntity as the SchemaObject data is not valid [{0}]",dataEntity.Name));
                }
            }
            else
            {
                throw new InvalidOperationException("Failed to build qulified name for DataEntity as it does not have any schema objects associated");
            }
            return name;
        }
    }
}
